﻿using System;
using System.Collections.Generic;
using System.Data;

using System.Text;
using HZ.DrugTrade;

using System.Data.SqlClient;
using HZ.Data;
using System.Data.OracleClient;
using HZ.DrugTrade.Model;

namespace HZ.DrugTrade.DAL
{
    public class DAL_Goods
    {
        /// <summary>
        /// 条件查询商品表中的投标企业
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.Goods> GetGoodsByWhere(HZ.DrugTrade.Model.Query.Goods where, int currentpage, int pagesize, out int totalrecord)
        {
            string wherestr = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,true);
            string sql = string.Format("SELECT * FROM View_Goods WHERE 1=1 and CompanyID_TB is not null {0}", wherestr);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize) { CurrentPage = currentpage };
            DataTable dt = pdes.GetSpecailPage();
            totalrecord = pdes.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.Goods>(dt);
        }

        /// <summary>
        /// 条件查询商品表中的非投标企业
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.Goods> GetUnGoodsByWhere(HZ.DrugTrade.Model.Query.Goods where, int currentpage, int pagesize, out int totalrecord)
        {
            string wherestr = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,false);
            string sql = string.Format("SELECT * FROM View_Goods WHERE 1=1 and CompanyID_TB is null {0}", wherestr);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize) { CurrentPage = currentpage };
            DataTable dt = pdes.GetSpecailPage();
            totalrecord = pdes.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.Goods>(dt);
        }

        /// <summary>
        /// 条件查询商品表中的全部企业
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentpage"></param>
        /// <param name="pagesize"></param>
        /// <param name="totalrecord"></param>
        /// <returns></returns>
        public static IList<HZ.DrugTrade.Model.View.Goods> GetAllGoodsByWhere(HZ.DrugTrade.Model.Query.Goods where, int currentpage, int pagesize, out int totalrecord)
        {
            string wherestr = HZ.DrugTrade.DAL2.DAL_Helper.GetWhereCondition(where,true);
            string sql = string.Format("SELECT * FROM View_Goods WHERE 1=1 {0}", wherestr);
            HZ.Data.PageDescribe pdes = new HZ.Data.PageDescribe(sql, pagesize) { CurrentPage = currentpage };
            DataTable dt = pdes.GetSpecailPage();
            totalrecord = pdes.TotalCount;
            return HZ.Data.DAL_Helper.CommonFillList<HZ.DrugTrade.Model.View.Goods>(dt);
        }

        /// <summary>
        /// 获取一条中标或不中标商品
        /// </summary>
        /// <param name="goodsID"></param>
        /// <returns></returns>
        public static HZ.DrugTrade.Model.View.Goods GetGoodsByID(string goodsID)
        {
            //oracle版本
            string sql = "SELECT  * FROM View_Goods WHERE goodsID = @goodsID and rownum<=1";

            OracleParameter para = new OracleParameter("@goodsID", goodsID);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(CommandType.Text, sql, new OracleParameter[] { para });
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            return HZ.Data.DAL_Helper.CommonFill<HZ.DrugTrade.Model.View.Goods>(dt.Rows[0]);

        }

        /// <summary>
        /// 获取生产企业对应产品
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static DataTable GetGoods(string userid)
        {
            string sql = "select  * from  View_ProcureCatalog where CompanyID_SC=@userid";
           // SqlParameter para = new SqlParameter("@userid", userid);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(CommandType.Text, sql, HZ.Data.DBParameterFactory.GetIDbDataParameter("@userid", userid));
            return dt;
        }
        /// <summary>
        /// 根据产品编号查询产品信息
        /// add by Meng 2013-03-25
        /// </summary>
        /// <param name="goodsid"></param>
        /// <returns></returns>
        public static HZ.DrugTrade.Model.Goods GetGoodsInfoByWhere(string goodsid)
        {
            string sql = string.Format(@"select * from view_procurecatalog_tb where goodsid = '{0}'",goodsid);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(CommandType.Text, sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            return HZ.Data.DAL_Helper.CommonFill<HZ.DrugTrade.Model.Goods>(dt.Rows[0]);
        }
        /// <summary>
        /// 更新产品信息
        /// </summary>
        /// <param name="goods"></param>
        /// <returns></returns>
        public static int UpdateGoodsInfo(HZ.DrugTrade.Model.Goods goods)
        {
            string sql = string.Format(@"begin  update tb_goods set GoodsName='{0}',Outlookc='{1}',Material='{2}',Brand='{3}',StandardName='{4}',StandardId='{5}',
                                         CompanyName_SC='{6}',Unit='{7}',QualityLevel='{8}',CPRZTX='{9}',RegCode='{10}',RegName='{11}',RegDate=to_date('{12}','yyyy-mm-dd HH24:mi:ss'),
                                         RegisterTime=to_date('{13}','yyyy-mm-dd HH24:mi:ss'),COMPANYADDRESS_SC='{14}',COMPANYLOCALADDRESS_SC='{15}',ProApplication='{16}',Performance='{17}'
                                         where goodsid='{18}';update view_procurecatalog_tb set GoodsName='{0}',Outlookc='{1}',Material='{2}',Brand='{3}',StandardName='{4}',StandardId='{5}',
                                         CompanyName_SC='{6}',Unit='{7}',QualityLevel='{8}',CPRZTX='{9}',RegCode='{10}',RegName='{11}',RegDate=to_date('{12}','yyyy-mm-dd HH24:mi:ss'),
                                         RegisterTime=to_date('{13}','yyyy-mm-dd HH24:mi:ss'),COMPANYADDRESS_SC='{14}',COMPANYLOCALADDRESS_SC='{15}',ProApplication='{16}',Performance='{17}'
                                         where goodsid='{18}';end;", goods.GoodsName, goods.Outlookc, goods.Material, goods.Brand, goods.StandardName, goods.StandardId, goods.CompanyName_SC,
                                                                goods.Unit, goods.QualityLevel, goods.CPRZTX, goods.RegCode, goods.RegName, goods.RegDate, goods.RegisterTime, goods.COMPANYADDRESS_SC,
                                                                goods.COMPANYLOCALADDRESS_SC, goods.ProApplication, goods.Performance, goods.GoodsID);
            return HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql);
        }
        /// <summary>
        /// 获取限价列表
        /// </summary>
        /// <param name="where"></param>
        /// <param name="currentPage"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public static DataTable GetLimitPriceByWhere(string where, int currentPage, int pageSize, out int totalRecord)
        {
            IDbHelper helper = DbHelperFactory.Create();
            string sql = string.Format(@"select a.*,goodsname,sortname,Outlookc,Material,count,Unit,QualityLevel,CompanyName_SC_tb,case isconfirm when 1 then '已确认' else '未确认' end confirmstate
                from TB_LimitedPrice a left join view_procurecatalog_tb b on a.goodsid = b.goodsid
                                        where 1=1 {0}", where);
            PageDescribe des = new PageDescribe(sql, pageSize);
            des.CurrentPage = currentPage;
            totalRecord = des.TotalCount;
            return des.GetSpecailPage();
        }
        /// <summary>
        /// 根据产品编号获取限价信息
        /// </summary>
        /// <param name="goodsid"></param>
        /// <returns></returns>
        public static LimitedPrice GetLimitedPriceByID(string goodsid)
        {
            string sql = string.Format(@"select * from TB_LimitedPrice where goodsid = '{0}'", goodsid);
            DataTable dt = HZ.Data.DbHelperFactory.Create().ExecuteTable(CommandType.Text, sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            return HZ.Data.DAL_Helper.CommonFill<HZ.DrugTrade.Model.LimitedPrice>(dt.Rows[0]);
        }

        /// <summary>
        /// 更新限价
        /// </summary>
        /// <param name="lp"></param>
        /// <returns></returns>
        public static int GetUpdatelimitPrice(LimitedPrice lp)
        {
            string sql = string.Format(@"update TB_LimitedPrice set ReferencePrice= '{0}',ActualPrice='{1}',LimitedPrice='{2}',UpdateLog = '{4}' where goodsid = '{3}'", lp.ReferencePrice, lp.ActualPrice, lp.LimitPrice, lp.Goodsid, lp.UpdateLog);
            return Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql));
        }

        /// <summary>
        /// 企业确认限价
        /// </summary>
        /// <param name="Goodsid"></param>
        /// <returns></returns>
        public static int GetConfirmLimitPrice(string Goodsid)
        {
            int falg = 0;
            string strsql = string.Format(@"select count(*) from TB_LimitedPrice where goodsid = '{0}' and isconfirm = 1",Goodsid);
            if (Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteScalar(strsql)) > 0)
            {
                falg = 2;
            }
            else
            {
                string sql = string.Format(@"update TB_LimitedPrice set isconfirm=1,isconfirmtime=sysdate,updatelog=updatelog||'企业已确认限价。' where goodsid = '{0}'", Goodsid);
                falg = Convert.ToInt32(HZ.Data.DbHelperFactory.Create().ExecuteNonQuery(sql));
            }
            return falg;
        }
    }
}
